﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Windows.Forms;
using System.Net;
using System.IO;
using System.Drawing;
using System.Globalization;
using System.Threading;
using System.Data.OleDb;
using System.Data.SqlClient ;
using Telerik.WinControls;
using Telerik.WinControls.UI;
using System.Data.OleDb;
using System.Data.Common;
using Telerik.WinControls.Data;
using Telerik.WinControls.UI;


namespace libDAL
{

    public static class clsGenFunction
    {
       private static  DbProviderFactory factory;
       private static DbConnection connection;

        public static List<string> GetSheetNames(string path)
        {
            List<string> sheets = new List<string>();
            try
            {
                factory = DbProviderFactories.GetFactory("System.Data.OleDb");
                connection = factory.CreateConnection();

                string connectionString = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""", path);
                connection.ConnectionString = connectionString;
                connection.Open();
                DataTable tbl = connection.GetSchema("Tables");
                
                foreach (DataRow row in tbl.Rows)
                {
                    string sheetName = (string)row["TABLE_NAME"];
                    if (sheetName.EndsWith("$"))
                    {
                        //sheetName = sheetName.Substring(0, sheetName.Length - 1);
                        sheetName = sheetName.Trim();
                    }
                    sheets.Add(sheetName);
                }
                connection.Close();
            }
            catch (Exception ex)
            {
                connection.Close();
                MessageBox.Show(ex.Message + " GetSheetNames"); 
            }
            return sheets;
        }

        private static OleDbConnection get_OpenOledbconnectionExcel(string strPath)
        {
            OleDbConnection oledbCon = new OleDbConnection();
            try
            {
                string strConnString = string.Empty;
                if (Path.GetExtension(strPath) == ".xlsx")
                {
                    strConnString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strPath + ";Extended Properties=Excel 12.0;";
                }
                else
                {
                    strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strPath + ";Extended Properties=Excel 8.0;";
                }

                oledbCon.ConnectionString = strConnString;

                if (oledbCon.State != ConnectionState.Open)
                {
                    oledbCon.Open();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.InnerException.Message); 
            }
            return oledbCon;
        }

        public static void set_BackStage(ref RadPageView rpvControl)
        {
            RadPageViewBackstageElement backStageElement = rpvControl.ViewElement as RadPageViewBackstageElement;
            StripViewItemContainer itemContainer = backStageElement.ItemContainer as StripViewItemContainer;
            StripViewItemLayout itemLayout = backStageElement.ItemContainer.ItemLayout as StripViewItemLayout;

            itemContainer.MinSize = new Size(100, 0);
        }

        public static DataTable get_ValueXlsx(string strPath, string strSheetName)
        {
            DataTable dtGetXlsx = new DataTable();
            try
            {
                OleDbConnection connection = get_OpenOledbconnectionExcel(strPath);//new OleDbConnection(connectionString);
                OleDbDataAdapter dataAdapter = new OleDbDataAdapter("select * from " + "[" + strSheetName + "]", connection);
                dataAdapter.Fill(dtGetXlsx);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_ValueXlsx");
            }
            return dtGetXlsx;
        }

        public static string get_GenKeyNEWID(cls_DAL _Server)
        {
            string strGenKey = string.Empty;
            try
            {
                  strGenKey= _Server.get_OneKey("Select NEWID() as GenKey", "String");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_GenKeyNEWID");
            }
            return strGenKey;
        }

        public static string get_HistoryID(string strCompanyID, cls_DAL _DAL)
        {
            return strCompanyID + "_" + get_DateDBServerFormat(_DAL);
        }

        public static DateTime get_ServerDateTimeRAW(cls_DAL _SERVER)
        {
            DateTime dt = DateTime.Now;
            try
            {
                DataTable dtable = new DataTable();
                dtable = _SERVER.get_TableDetail("select getdate()");
                if (dtable.Rows.Count > 0)
                {
                    dt = Convert.ToDateTime(dtable.Rows[0][0]);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_ServerDateTimeRAW");
            }
            return dt;
        }

        public static bool get_CheckCharset(string strEntry)
        {
            bool bolStatus = false;

            try
            {
                if (System.Text.RegularExpressions.Regex.IsMatch(strEntry, @"^[a-zA-Z]") == true)
                {
                    bolStatus = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_CheckCharset"); 
            }
            return bolStatus;
        }

        public static DateTime get_DateDBServerFormat(cls_DAL objBULDate)
        {
            DateTime dt = new DateTime();
            try
            {
                dt = objBULDate.get_ServerDate_ddMMyyyy("select GETDATE() AS ddMMyyyy");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_DateddMMyyyyFormat");
            }
            return dt;
        }

        public static int get_OracleDateFormat(DateTime dtGregDate)
        {
            int intOracleDate = 0;
            try
            {
                string strCentury = String.Format("{0:yyyy}", dtGregDate).Substring(0, 2);
                string strMonthYear = string.Format("{0:MMddyyyy}", dtGregDate).Substring(6, 2);

                string strYearNowStart = "01/01/" + strCentury + Convert.ToInt32(strMonthYear).ToString();
                TimeSpan dtDateDiff = DateTime.Now - Convert.ToDateTime(strYearNowStart);
                string abc = dtDateDiff.Days.ToString();
                if (Convert.ToInt32(abc) < 100)
                {
                    int a = Convert.ToInt32(abc) + 1;
                    abc = "0" + a.ToString();
                }
                string strOracleTime = (Convert.ToInt32(strCentury.Substring(0, 1)) - 1).ToString() + strMonthYear + abc;
                intOracleDate = Convert.ToInt32(strOracleTime);

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_OracleDateFormat");
            }
            return intOracleDate;
        }

        public static string get_OracleTimeFormat(cls_DAL _SERVER)
        {

            string strOracleTime = string.Empty;
            string strQuery = string.Empty;
            try
            {
                strQuery = "Select Cast(DatePart(Hour,GetDate()) as varchar(2)) + Cast(DatePart(Minute,GetDate()) as varchar(2)) + Cast(DatePart(Second,GetDate()) as varchar(2)) as [Oracle Time]";
                DataTable dtOracleTime = new DataTable();
                dtOracleTime = _SERVER.get_TableDetail(strQuery);
                strOracleTime = dtOracleTime.Rows[0]["Oracle Time"].ToString();

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_OracleTimeFormat");
            }
            return strOracleTime;

        }

        public static int get_OracleDateFormat(cls_DAL _SERVER)
        {
            int intOracleDate = 0;
            string strQuery = string.Empty;
            DateTime dateOracleDate;
            try
            {
                strQuery = "Select GetDate() as [Oracle Date]";
                DataTable dtOracleDate = new DataTable();
                dtOracleDate = _SERVER.get_TableDetail(strQuery);
                dateOracleDate = Convert.ToDateTime(dtOracleDate.Rows[0]["Oracle Date"].ToString());

                string strCentury = String.Format("{0:yyyy}", dateOracleDate).Substring(0, 2);
                string strMonthYear = string.Format("{0:MMddyyyy}", dateOracleDate).Substring(6, 2);

                string strYearNowStart = "01/01/" + strCentury + Convert.ToInt32(strMonthYear).ToString();
                TimeSpan dtDateDiff = dateOracleDate.Date - Convert.ToDateTime(strYearNowStart);
                string abc = dtDateDiff.Days.ToString();
                if (Convert.ToInt32(abc) < 100)
                {
                    int a = Convert.ToInt32(abc) + 1;
                    abc = "0" + a.ToString();
                }
                string strOracleTime = (Convert.ToInt32(strCentury.Substring(0, 1)) - 1).ToString() + strMonthYear + abc;
                intOracleDate = Convert.ToInt32(strOracleTime);

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_OracleDateFormat");
            }
            return intOracleDate;
        }

        public static int get_GregorianToOracle(DateTime dtGregDate)
        {
            int intOracleDate = 0;
            try
            {
                string strCentury = String.Format("{0:yyyy}", dtGregDate).Substring(0, 2);
                string strMonthYear = string.Format("{0:MMddyyyy}", dtGregDate).Substring(6, 2);

                string strYearNowStart = "01/01/" + strCentury + Convert.ToInt32(strMonthYear).ToString();
                TimeSpan dtDateDiff = dtGregDate.Date - Convert.ToDateTime(strYearNowStart);
                string abc = dtDateDiff.Days.ToString();
                if (Convert.ToInt32(abc) < 100)
                {
                    int a = Convert.ToInt32(abc);
                    abc = "0" + a.ToString();
                }
                abc = Convert.ToString(Convert.ToInt32(abc) + 1);
                string strOracleTime = (Convert.ToInt32(strCentury.Substring(0, 1)) - 1).ToString() + strMonthYear + abc;
                intOracleDate = Convert.ToInt32(strOracleTime);

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_GregorianToOracle");
            }
            return intOracleDate;
        }

        public static void set_getScreenShot(string strType)
        {
            Rectangle bounds = Screen.GetBounds(Point.Empty);
            using (Bitmap bitmap = new Bitmap(bounds.Width, bounds.Height))
            {
                using (Graphics g = Graphics.FromImage(bitmap))
                {
                    g.CopyFromScreen(Point.Empty, Point.Empty, bounds.Size);
                }
                string AppPath = Environment.CurrentDirectory;
                string fileName = DateTime.Now.ToString("dd_MM_yyyy") + " " + DateTime.Now.ToString("hh_mm_ss");
                bitmap.Save(AppPath + @"\\Sysfiles\\" + strType + "_" + fileName + ".jpg");
            }

        }

        public static bool set_Createfolder(string strDirectoryPath)
        {
            bool bolStatus = false;
            try
            {
                DirectoryInfo dir = new DirectoryInfo(strDirectoryPath);
                if (!dir.Exists)
                {
                    dir.Create();
                    bolStatus = true;
                }
                else
                {
                    FileInfo[] files = dir.GetFiles();
                    for (int intIndex = 0; intIndex < files.Length; intIndex++)
                    {
                        set_DeleteFile(files[intIndex].ToString().Trim());

                    }
                    bolStatus = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_Createfolder"); 
            }
            return bolStatus;
        }

        public static string getHostName1()
        {
            return Dns.GetHostName();
        }

        public static string getHostIP()
        {
            IPAddress[] localIPs = Dns.GetHostAddresses(Dns.GetHostName());
            return localIPs[0].ToString() + "_" + localIPs[1].ToString();
        }

        public static string getHostName()
        {
            return System.Security.Principal.WindowsIdentity.GetCurrent().Name;
        }

        public static DateTime get_TimehhmmttFormat(cls_DAL objBULTime)
        {
            DateTime dt = new DateTime();
            try
            {
                dt = objBULTime.get_ServerDate_ddMMyyyy("select RIGHT('0' + LTRIM(STUFF(RIGHT(CONVERT(CHAR(26), CURRENT_TIMESTAMP, 109), 14),9, 4, ' ')),11) as hhmmsstt");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_TimehhmmttFormat");
            }
            return dt;
        }



        public static DateTime get_DateMMddyyyyFormat(cls_DAL objBULDate)
        {
            DateTime dt = new DateTime();
            try
            {
                dt = objBULDate.get_ServerDate_ddMMyyyy("select CONVERT(VARCHAR(10), GETDATE(), 101) AS MMddyyyy");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_DateddMMyyyyFormat");
            }
            return dt;
        }

        public static DateTime get_OracleToGregorian(string strHijriDate)
        {
            DateTime dteGregorian = DateTime.Now;
            try
            {
                string strlast3Chars = strHijriDate.Substring(3, 3);
                string strFirtChar = strHijriDate.Substring(0, 1);
                string strCharYearStart = string.Empty;
                string strCharYearEnd = string.Empty;
                DateTime dteYearConcat;
                DateTime dteYearFinal;

                if (strFirtChar == "0")
                {
                    strCharYearStart = "19";
                }
                else if (strFirtChar == "1")
                {
                    strCharYearStart = "20";
                }
                strCharYearEnd = strHijriDate.Substring(1, 2);
                dteYearConcat = Convert.ToDateTime("1/1/" + strCharYearStart + strCharYearEnd);

                dteYearFinal = dteYearConcat.AddDays(Convert.ToInt32(strlast3Chars) - 1);
                dteGregorian = dteYearFinal;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_OracleToGregorian");
            }
            return dteGregorian;
        }

        public static Boolean set_DeleteFile(string strPathFrom)
        {
            Boolean bool_Deleted = false;
            try
            {
                if (strPathFrom.Trim() != string.Empty)
                {
                    if (System.IO.File.Exists(strPathFrom) == true)
                    {
                        // Use a try block to catch IOExceptions, to handle the case of the file already being opened by another process.
                        try
                        {
                            System.IO.File.Delete(strPathFrom);
                            bool_Deleted = true;
                        }
                        catch (System.IO.IOException e)
                        {
                            MessageBox.Show(e.Message + " File.Delete");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_DeleteFile");
            }
            return bool_Deleted;
        }

        public static Boolean set_SaveFile(string strPathFrom, string strPathDestination)
        {
            Boolean bool_Saved = false;
            try
            {
                if (strPathFrom.Trim() != string.Empty)
                {
                    int intFNIndex = strPathDestination.LastIndexOf(@"\");
                    string strFileName = strPathDestination.Substring(intFNIndex + 1, strPathDestination.Length - intFNIndex - 1);
                    if (get_checkFileNameExists(strPathDestination) == false)
                    {
                        File.Copy(strPathFrom, strPathDestination);
                        bool_Saved = true;
                    }
                    else
                    {
                        var result = MessageBox.Show("Image file name: " + strFileName + " already exists, do you want to overight the previous file Y/N?", "Verification", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                        if (result == DialogResult.Yes)
                        {
                            File.Copy(strPathFrom, strPathDestination, true);
                            bool_Saved = true;
                        }
                    }
                }
                else
                {
                    MessageBox.Show("No image found.");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " set_Save");
            }
            return bool_Saved;
        }

        private static Boolean get_checkFileNameExists(string strPath)
        {
            Boolean bool_Status = false;
            try
            {
                bool_Status = File.Exists(strPath);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_checkFileNameExists");
            }
            return bool_Status;
        }

        public static int get_checkComputeNullInt(object objValue)
        {
            int intComputation = 0;
            if (objValue != null && objValue.ToString() != string.Empty)
            {
                intComputation = Convert.ToInt32(objValue);
            }
            return intComputation;
        }

       

        public static double get_checkComputeNullDbl(object objValue)
        {
            double dblComputation = 0;
            if (objValue != null && objValue != DBNull.Value)
            {
                dblComputation = Convert.ToDouble(objValue);
            }
            return dblComputation;
        }

        public static string get_checkStringNull(object objValue)
        {
            string strValue = string.Empty;
            if (objValue != null && objValue != DBNull.Value)
            {
                strValue = objValue.ToString().Trim();
            }
            return strValue;
        }


        public static DateTime get_CheckDateTimeNull(object objValue)
        {
            DateTime dteDefault = new DateTime();
            try
            {
                if (objValue != null && objValue != DBNull.Value)
                {
                    dteDefault = Convert.ToDateTime(objValue);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_CheckDateTimeNull"); 
            }
            return dteDefault;
        }

        public static string get_ConvertQ(string strInputText)
        {
            string strValue = string.Empty;
            try
            {
                strValue = strInputText.Replace("\'", "\'\'");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_ConvertQ"); 
            }
            return strValue;
        }

        public static void set_RemoveColumn(DataColumn colToRemove, ref DataTable dtFinalReport)
        {
            DataColumnCollection columns;
            // Get the DataColumnCollection from a DataTable in a DataSet.
            columns = dtFinalReport.Columns;

            if (columns.Contains(colToRemove.ColumnName))
            {
                columns.Remove(colToRemove);
                dtFinalReport.AcceptChanges();
            }
        }

        public static string get_FilePathDialog(string strFilter)
        {
            string strPath = string.Empty;
            try
            {
                OpenFileDialog openFileDialogPath = new OpenFileDialog();
                openFileDialogPath.Filter = strFilter;
                openFileDialogPath.FilterIndex = 1;
                openFileDialogPath.Multiselect = false;

                if (openFileDialogPath.ShowDialog() == DialogResult.OK)
                {
                    strPath = openFileDialogPath.FileName;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_StrFilePathDialog");
            }
            return strPath;
        }

        public static string get_FolderPathDialog()
        {
            string strPath = string.Empty;
            try
            {
                FolderBrowserDialog openFolderDialogPath = new FolderBrowserDialog();
                if (openFolderDialogPath.ShowDialog() == DialogResult.OK)
                {
                    strPath = openFolderDialogPath.SelectedPath;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_StrFilePathDialog");
            }
            return strPath;
        }

        public static string get_NewIDPart(cls_DAL _SERVER)
        {
            string strID = string.Empty;
            try
            {
                DataTable dtNewID = _SERVER.get_TableDetail("Select NEWID() as ID");
                if (dtNewID.Rows.Count > 0)
                {
                    strID = dtNewID.Rows[0]["ID"].ToString().Trim().Substring(7, 7);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_NewIDPart");
            }
            return strID;
        }

        public static DateTime get_DateTimeMMddyyyyhhmmssFormat(cls_DAL _SERVER)
        {
            DateTime ServerDateTime = DateTime.Now;

            try
            {
                ServerDateTime = _SERVER.get_ServerDate_ddMMyyyy("select GETDATE()");

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_DateTimeMMddyyyyhhmmssFormat");
            }

            return ServerDateTime;
        }

        public static Boolean get_PrintBarcodeTiming(cls_DAL _SERVER, DateTime dateProductionDate, int intMinutes)
        {
            Boolean boolBarcodeTimeExpired = false;
            try
            {
                string strServerDate = clsGenFunction.get_DateMMddyyyyFormat(_SERVER).ToString("MM/dd/yyyy");
                string strServerTime = clsGenFunction.get_TimehhmmttFormat(_SERVER).ToString("hh:mm tt");
                DateTime dateServerTime = Convert.ToDateTime(strServerDate + " " + strServerTime);
                DateTime dateProductionDateSubtructed;

                dateProductionDateSubtructed = dateProductionDate.AddMinutes(intMinutes);

                if (dateServerTime > dateProductionDateSubtructed)
                {
                    boolBarcodeTimeExpired = true;
                }
                else
                {
                    boolBarcodeTimeExpired = false;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_PrintBarcodeTiming");
            }

            return boolBarcodeTimeExpired;
        }

        public static decimal get_YieldRatio(decimal decYieldStress, decimal decTensileStress)
        {
            decimal decYieldRatio = 0;
            try
            {

                decYieldRatio = (decYieldStress / decTensileStress);


            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_YieldRatio");
            }
            return decYieldRatio;
        }

        public static Boolean get_CheckYesNo(string strYesOrNo)
        {
            Boolean boolYesNo = false;
            try
            {
                string strYesNo;
                strYesNo = strYesOrNo.ToUpper();

                if (strYesNo != "YES" && strYesNo != "NO")
                {
                    boolYesNo = false;
                }
                else
                {
                    boolYesNo = true;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + " get_CheckYesNo");
            }
            return boolYesNo;

        }

    }
}
